package Dao;
 
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import Dao.DBConn;
import Model.MemberBean;
 
public class MemberDAO {
    private Connection conn; 
    public MemberDAO() throws Exception {
        conn = DBConn.getConnection();
    }
    
    //根据ID获取用户信息
    public List<MemberBean> getMember(int MemberID) throws Exception{
        List<MemberBean> ret = new ArrayList<MemberBean>();
        try{
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from member where MEMBER_ID='"+ MemberID + "'");
            while (rs.next()){
            	MemberBean member = new MemberBean();
            	member.setID(rs.getInt("MEMBER_ID"));
            	member.setName(rs.getString("MEMBER_NAME"));
            	member.setAddress(rs.getString("MEMBER_ADDRESS"));
            	member.setOccupation(rs.getString("MEMBER_OCCUPATION"));               
                ret.add(member);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.getMessage();
        }
        return ret;
    }
    
    //根据姓名查询会员信息
    public List<MemberBean> getMemberByName(String MemberName) throws Exception{
        List<MemberBean> ret = new ArrayList<MemberBean>();
        try{
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from member where MEMBER_NAME='"+ MemberName + "'");
            while (rs.next()){
            	MemberBean member = new MemberBean();
            	member.setID(rs.getInt("MEMBER_ID"));
            	member.setName(rs.getString("MEMBER_NAME"));
            	member.setAddress(rs.getString("MEMBER_ADDRESS"));
            	member.setOccupation(rs.getString("MEMBER_OCCUPATION"));               
                ret.add(member);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch (Exception e){
            e.getMessage();
        }
        return ret;
    }
    // 添加新会员
    public void insertMember(MemberBean member) throws Exception{
    	String sql = "insert into member(MEMBER_ID,MEMBER_NAME,MEMBER_ADDRESS,MEMBER_OCCUPATION) values(?,?,?,?)";
        try{
            PreparedStatement prestmt = conn.prepareStatement(sql);
            prestmt.setInt(1, member.getID());
            prestmt.setString(2, member.getName());            
            prestmt.setString(3, member.getAddress());
            prestmt.setString(4, member.getOccupation());    
            prestmt.execute();
            prestmt.close();
            conn.close();
        }catch (Exception e){
            e.getMessage();
        }
    }
 
}